package dr.board.dao;

import java.sql.*;
import java.util.*;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import dr.board.domain.Board;

public class BoardDAO {
	
	private static BoardDAO instance = new BoardDAO();
	
	public static BoardDAO getInstance(){
		return instance;
	}
	
	private BoardDAO(){}
	
	/*-------------------------------------------------------------------- DB 연결 ---------------------*/
	private Connection getConnection() throws Exception{
		Context initCtx = new InitialContext();
		Context envCtx = (Context)initCtx.lookup("java:comp/env");
		DataSource ds = (DataSource)envCtx.lookup("jdbc/orcl");
		return ds.getConnection();
	}
	
	/*----------------------------------------------------------------게시판의 글쓰기 처리-----------------*/
	public void insertArticle(Board board)throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		int num = board.getNum();
		int ref = board.getRef();
		int re_step = board.getRe_step();
		int re_level = board.getRe_level();
		int number = 0;
		String sql="";
		
		try{
			conn = getConnection();
			int cnt=0;
			if(num != 0){ //답글일 경우만 수행
				/* 답글 작성시 같은 ref 범위에서 writeForm에서 넘어온
				   re_step 값보다 큰 re_step이 있으면 1씩 증가 */
				sql = "update BOARD set re_step = re_step+1 where ref=? and re_step > ?";
				pstmt =conn.prepareStatement(sql);
				pstmt.setInt(1, ref);
				pstmt.setInt(2, re_step);
				pstmt.executeUpdate();
				
				re_step = re_step+1;
				re_level = re_level+1;
				
			}else{ // 일반글일경우 수행
				pstmt = conn.prepareStatement("select max(num) from BOARD");
				//ref 번호 부여하기 위해 수행
				rs = pstmt.executeQuery();
				if(rs.next()){
					number = rs.getInt(1)+1;
				}else{ // 최초 글 작성시
					number = 1;
				}
				ref = number;
				re_step = 0;
				re_level = 0;
				
			}
			// 쿼리를 작성
			
			sql = "insert into BOARD(num,id,subject,passwd,reg_date,ref,re_step,re_level,content,ip) "+
			                 "values (board_seq.nextval,?,?,?,sysdate,?,?,?,?,?)" ;
			pstmt= conn.prepareStatement(sql);
			pstmt.setString(++cnt,board.getId() );
			pstmt.setString(++cnt,board.getSubject() );
			pstmt.setString(++cnt,board.getPasswd() );
			pstmt.setInt(++cnt, ref);
			pstmt.setInt(++cnt, re_step);
			pstmt.setInt(++cnt, re_level);
			pstmt.setString(++cnt,board.getContent() );
			pstmt.setString(++cnt,board.getIp() );
			
			pstmt.executeUpdate();
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(pstmt != null)try{pstmt.close();}catch(SQLException e){}
			if(conn != null)try{conn.close();}catch(SQLException e){}
		}
	}
	
	/*--------------------------------------------------------------- 전체 글목록의 수를 얻어냄  ---------------------*/
	public int getArticleCount(String keyField, String keyWord)throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs =null;
		String sql="";
		
		int x = 0;
		
		try{
			conn = getConnection();
			if(keyWord == null || "".equals(keyWord.trim())){ // 그냥 목록
				
				sql = "select count(*) from BOARD";
				pstmt = conn.prepareStatement(sql);
			
			}else{                                            // 검색했을때 목록 보여주기~~
			    //  keyField = 컬럼명 ,keyWord = 데이터
				keyField=new String(keyField.getBytes("iso-8859-1"), "utf-8"); //한글 필터
		    	keyWord=new String(keyWord.getBytes("iso-8859-1"), "utf-8"); //한글 필터
		    	System.out.println(keyWord);
				sql = "select count(*) from BOARD where "+keyField+" like ?";
				pstmt=conn.prepareStatement(sql);
				pstmt.setString(1, "%"+keyWord+"%");
			}
			
			rs=pstmt.executeQuery();
			
			if(rs.next()){
				x=rs.getInt(1);  //
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(rs != null)try{rs.close();}catch(SQLException e){}
			if(pstmt != null)try{pstmt.close();}catch(SQLException e){}
			if(conn != null)try{conn.close();}catch(SQLException e){}
		}
		return x;
	}
	
	/*-------------------------------------------------------------------- 게시판의 글목록 보기  ---------------------*/
	public List getArticles(int start, int end, String keyField, String keyWord)throws Exception{
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List articleList = null;
		String sql = "";
		
		try{
			conn = getConnection();
			
			if(keyWord == null || "".equals(keyWord.trim())){  /*------------ 목록---------*/
				System.out.println(keyField);
				sql = "select *"+
				 	  "from (select a.* , rownum rnum "+
				 	         "from (select * "+
				 	               "from BOARD "+
				                   "order by ref desc, re_step asc)a) " +
				      "where rnum >= ? and rnum <=?";	
				pstmt = conn.prepareStatement(sql);
				pstmt.setInt(1, start);
				pstmt.setInt(2, end);
				
			}else{				/*--------------------------------------------검색 목록---------*/
				keyField=new String(keyField.getBytes("iso-8859-1"), "utf-8"); //한글 필터
		    	keyWord=new String(keyWord.getBytes("iso-8859-1"), "utf-8"); //한글 필터
		    	System.out.println(keyWord);
				sql = "select *"+
			 	      "from (select a.* , rownum rnum "+
			 	             "from (select * "+
			 	                   "from BOARD "+
			 	                   "where "+keyField+" like ? "+
			                       "order by ref desc, re_step asc)a) " +
			          "where rnum >= ? and rnum <=?";					
				pstmt = conn.prepareStatement(sql);				
				pstmt.setString(1, "%"+keyWord+"%");
				pstmt.setInt(2, start);
				pstmt.setInt(3, end);
			}
			 		
			
			rs=pstmt.executeQuery();
			
			if(rs.next()){
				articleList = new ArrayList(end);
				do{
					Board article = new Board();
					article.setNum(rs.getInt("num"));
					article.setId(rs.getString("id"));
					article.setSubject(rs.getString("subject"));
					article.setPasswd(rs.getString("passwd"));
					article.setReg_date(rs.getTimestamp("reg_Date"));
					article.setReadcount(rs.getInt("readcount"));
					article.setRef(rs.getInt("ref"));
					article.setRe_step(rs.getInt("re_Step"));
					article.setRe_level(rs.getInt("re_Level"));
					article.setContent(rs.getString("content"));
					article.setIp(rs.getString("ip"));
					
					articleList.add(article);					
				}while(rs.next());				
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(rs != null) try{rs.close();}catch(SQLException e){}
			if(pstmt != null) try{pstmt.close();}catch(SQLException e){}
			if(conn != null) try{conn.close();} catch(SQLException e){}
		}
		return articleList;
	}
	
	
	/*--------------------------------------------------------------------  조회수 & 상세보기 ---------------------*/
	public Board getArticle(int num) throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Board article = null;
		
		try{
			//조회수
			conn = getConnection();
			pstmt = conn.prepareStatement("update BOARD set readcount = readcount + 1 where num=?");
			pstmt.setInt(1, num);			
			pstmt.executeUpdate();
				
			// 상세내용
			pstmt= conn.prepareStatement("select * from BOARD where num=?");
			pstmt.setInt(1, num);
			rs= pstmt.executeQuery();
			
			if (rs.next()) {
				article = new Board();
	            
				article.setNum(rs.getInt("num"));
				article.setId(rs.getString("id"));
				article.setSubject(rs.getString("subject"));
				article.setPasswd(rs.getString("passwd"));
				article.setReg_date(rs.getTimestamp("reg_date"));
				article.setReadcount(rs.getInt("readcount"));
	            article.setRef(rs.getInt("ref"));
	            article.setRe_step(rs.getInt("re_step"));
	            article.setRe_level(rs.getInt("re_level"));
	            article.setContent(rs.getString("content"));
	            article.setIp(rs.getString("ip"));     
				}
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(rs != null) try{rs.close();}catch(SQLException e){}
			if(pstmt != null) try{pstmt.close();}catch(SQLException e){}
			if(conn != null) try{conn.close();} catch(SQLException e){}
		}
		return article;
	}
	
	
	/*-------------------------------------------------------- 상세보기 수정버튼 클릭시 내용 ---------------------*/
	
	public Board updateGetArticle(int num) throws Exception{
		Connection conn= null;
		PreparedStatement pstmt =null;
		ResultSet rs = null;
		Board article=null;
		
		try{
			conn = getConnection();
			pstmt= conn.prepareStatement("select * from board where num = ?");
		    
			pstmt.setInt(1, num);			
			rs = pstmt.executeQuery();
			
			 if (rs.next()) {
	                article = new Board();
	                article.setNum(rs.getInt("num"));
					article.setId(rs.getString("id"));
	                article.setSubject(rs.getString("subject"));
	                article.setPasswd(rs.getString("passwd"));
				    article.setReg_date(rs.getTimestamp("reg_date"));
					article.setReadcount(rs.getInt("readcount"));
	                article.setRef(rs.getInt("ref"));
	                article.setRe_step(rs.getInt("re_step"));
					article.setRe_level(rs.getInt("re_level"));
	                article.setContent(rs.getString("content"));
				    article.setIp(rs.getString("ip"));     
				}
			 
		}catch(Exception e){
			e.printStackTrace();
		}finally{
       		if (rs != null) try { rs.close(); } catch(SQLException ex) {}
			if(pstmt != null)try{conn.close();}catch(SQLException e){}
			if(conn != null )try{conn.close();}catch(SQLException e){}
		}
		return article;
	}
	
	
	/*------------------------------------------------------------------- 상세보기 수정---------------------*/
	public int updateArticle(Board article)throws Exception{
		Connection conn = null;
		PreparedStatement pstmt =null;
		ResultSet rs = null;
		
		String dbPasswd = "";	
		String sql="";
		int x=-1;
		try{
			conn =getConnection();
			pstmt=conn.prepareStatement("select passwd from BOARD where num=?");
			pstmt.setInt(1, article.getNum());
			rs = pstmt.executeQuery();
			int cnt=0;
			if(rs.next()){  //검증
				dbPasswd = rs.getString("passwd");
				if(dbPasswd.equals(article.getPasswd())){
					sql="update BOARD set id=?,subject=?";
					sql+=",content=? where num=?";
					pstmt= conn.prepareStatement(sql);
					
					 pstmt.setString(++cnt, article.getId());
		             pstmt.setString(++cnt, article.getSubject());
		             pstmt.setString(++cnt, article.getContent());
					 pstmt.setInt(++cnt, article.getNum());
		             pstmt.executeUpdate();
		             
		             x =1;		                
				}else{
					x =0;					
				}
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if (rs != null) try { rs.close(); } catch(SQLException ex) {}
			if(pstmt != null)try{conn.close();}catch(SQLException e){}
			if(conn != null )try{conn.close();}catch(SQLException e){}
		}
		return x;
	}
	
	
	/*------------------------------------------------------------------------------ 글 삭제 ---------------------*/
	public int deleteArticle(int num, String passwd) throws Exception{
		Connection conn =null;
		PreparedStatement pstmt =null;
		ResultSet rs = null;
		String dbPasswd="";
		int x = -1;
		
		try{
			conn =getConnection();
			pstmt=conn.prepareStatement("select passwd from BOARD where num=?");
			pstmt.setInt(1, num);
			rs = pstmt.executeQuery();
			
			if(rs.next()){  //검증
				dbPasswd = rs.getString("passwd");
				if(dbPasswd.equals(passwd)){
					
					pstmt= conn.prepareStatement( "delete from board where num=?");
				    pstmt.setInt(1, num);
		            pstmt.executeUpdate();		             
		            x =1;	//글삭제 성공	                
				}else{
					x =0;	 //비밀번호 틀림				
				}
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if (rs != null) try { rs.close(); } catch(SQLException ex) {}
			if(pstmt != null)try{conn.close();}catch(SQLException e){}
			if(conn != null )try{conn.close();}catch(SQLException e){}
		}
		
		return x;
	}
}

